﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

/*
    * Option 2 : Add new Item: Linq to SQL Classes.
    * Tạo thư mục LINQ => using: LINQ.
    * Xử lý bình thường như bên Winform
    */
using LINQ;
public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        FillProductsData();
    }

    //Show Data...
    void FillProductsData()
    {
        NorthwindDataContext data = new NorthwindDataContext();
        var query = from p in data.Products
                    select p;
        gvwData.DataSource = query;
        gvwData.DataBind();
    }
    void FillSupplierData()
    {
        NorthwindDataContext data = new NorthwindDataContext();
        var query = from p in data.Suppliers
                    select p;
        gvwData.DataSource = query;
        gvwData.DataBind();
    }
    void FillCategoryData()
    {
        NorthwindDataContext data = new NorthwindDataContext();
        var query = from p in data.Categories
                    select p;
        gvwData.DataSource = query;
        gvwData.DataBind();
    }
    protected void btnShow_Click(object sender, EventArgs e)
    {
        int choice = ddlTable.SelectedIndex;
        switch (choice)
        {
            case 0:
                break;
            case 1:
                FillProductsData();
                break;
            case 2:
                FillSupplierData();
                break;
            case 3:
                FillCategoryData();
                break;
        }
    }
    protected void btnRefresh_Click(object sender, EventArgs e)
    {
        FillProductsData();
    }

    //Search....
    void SearchProductByCategoryName(string name)
    {
        NorthwindDataContext data = new NorthwindDataContext();
        var query = from p in data.Products
                    where p.Category.CategoryName.StartsWith(name)
                    select new
                    {
                        ProductID = p.ProductID,
                        ProductName = p.ProductName,
                        CategoryID = p.CategoryID,
                        CategoryName = p.Category.CategoryName,
                        CategoryDescription = p.Category.Description
                    };
        gvwData.DataSource = query;
        gvwData.DataBind();
    }
    void SearchProductBySpplierName(string name)
    {
        NorthwindDataContext data = new NorthwindDataContext();
        var query = from p in data.Products
                    where p.Supplier.CompanyName.StartsWith(name)
                    select new
                    {
                        ProductID = p.ProductID,
                        ProductName = p.ProductName,
                        CategoryID = p.CategoryID,
                        SupplierID = p.SupplierID,
                        CompanyName = p.Supplier.CompanyName,
                        QuantityPerUnit = p.QuantityPerUnit,
                        UnitPrice = p.UnitPrice,
                        UnitsInStock = p.UnitsInStock,
                        UnitsOnOrder = p.UnitsOnOrder,
                        ReorderLevel = p.ReorderLevel,
                        Discontinued = p.Discontinued
                    };
        gvwData.DataSource = query;
        gvwData.DataBind();
    }
    void SearchProductByName(string name)
    {
        NorthwindDataContext data = new NorthwindDataContext();
        var query = from p in data.Products
                    where p.ProductName.StartsWith(name)
                    select p;
        gvwData.DataSource = query;
        gvwData.DataBind();

    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        int choice = ddlSearch.SelectedIndex;
        switch (choice)
        {
            case 0:
                break;
            case 1:
                SearchProductByName(txtSearch.Text);
                break;
            case 2:
                SearchProductBySpplierName(txtSearch.Text);
                break;
            case 3:
                SearchProductByCategoryName(txtSearch.Text);
                break;
        }
    }
    /*
     * Tạo event btnCount-Click để hiển thị: Số kiểu (Type) products được sản xuất bởi một Supplier.
     * Và tổng số sản phẩm đang có trong kho hàng (Products.UnitsInStock)
     */

    protected void btnCount_Click(object sender, EventArgs e)
    {
        
        NorthwindDataContext data = new NorthwindDataContext();
        /*Sử dụng group by trong Linq :
         *from p in data.Products group p by new { p.SupplierID, p.Supplier } into g
         * Truy cap vao cac phan tu trong nhom dung : Key
         * 
         */
        var query = from p in data.Products
                    group p by new { p.SupplierID, p.Supplier } into g
                    where g.Key.SupplierID == g.Key.Supplier.SupplierID
                    select new
                    {
                        g.Key.SupplierID,
                        g.Key.Supplier.CompanyName,
                        CountProductType = g.Key.Supplier.Products.Count(),
                        SumUnitsInStock = g.Key.Supplier.Products.Sum(s => s.UnitsInStock)
                    };
        gvwData.DataSource = query;
        gvwData.DataBind();
    }
    /**
     * event btnSum_click để hiển thị một cột là Sum(Products.UnitPrice * Products.UnitsInStock) của một loại Category.CategoryID
     */
    protected void btnSum_Click(object sender, EventArgs e)
    {
        NorthwindDataContext data = new NorthwindDataContext();
        var query = from p in data.Products
                    group p by new {p.Category, p.CategoryID} into g
                    where g.Key.CategoryID == g.Key.Category.CategoryID
                    select new
                    {
                        g.Key.CategoryID,
                        g.Key.Category.CategoryName,
                        CountProductType = g.Key.Category.Products.Count,
                        TotalProducts = g.Key.Category.Products.Sum(s=>s.UnitsInStock),
                        SumPrice = g.Key.Category.Products.Sum(c=>c.UnitPrice*c.UnitsInStock)
                    };
        gvwData.DataSource = query;
        gvwData.DataBind();

    }
}